﻿USE [HHMMS]
GO
/****** Object:  UserDefinedFunction [dbo].[UFN_SplitToNVarchar100]    Script Date: 04/29/2013 13:33:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[UFN_SplitToNVarchar100]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
BEGIN
execute dbo.sp_executesql @statement = N'

CREATE FUNCTION [dbo].[UFN_SplitToNVarchar100](@SourceStr NVARCHAR(MAX),@Separator NVARCHAR(10))   
RETURNS @temp table(SS NVARCHAR(100))  
/*  
Purpose:实现split功能 的函数   
@SourceStr 需要分解的字符串;  
@Separator 分隔符  
Return:返回分解之后的表变量,记得是列是“SS”(SubStr)  
Remark:  
    1.@Separator为Null Or 空字串时，返回@SourceStr本身的内容  
    2.结果不适合作为大数据量操作时的连接对象  
    3.@SourceStr与@Separator必须做前后去空格处理，原因是Len不计算尾随空格  
*/   
AS   
BEGIN  
    --检查参数  
	SET @SourceStr=LTRIM(RTRIM(@SourceStr))  
    IF(@SourceStr IS NULL OR @SourceStr=N'''')  
    BEGIN  
        RETURN  
    END  
    
    SET @Separator=LTRIM(RTRIM(@Separator))  
      
    IF(@Separator IS NULL OR @Separator=N'''')  
    BEGIN  
        INSERT INTO @temp(SS)  
        VALUES(@SourceStr)  
          
        RETURN  
    END  
      
    
    DECLARE @i INT  
    SET @i = CHARINDEX(@Separator,@SourceStr);  
    WHILE @i >= 1  
    BEGIN  
        INSERT @temp VALUES(LEFT(@SourceStr, @i-1));  
        SET @SourceStr = SUBSTRING(@SourceStr, @i+LEN(@separator), LEN(@SourceStr) - @i);  
          
        SET @SourceStr=LTRIM(RTRIM(@SourceStr))  
        SET @i = CHARINDEX(@Separator, @SourceStr);  
    END  
      
    IF @SourceStr <> ''''  
    BEGIN   
        INSERT @temp VALUES(@SourceStr)  
    END      
          
    RETURN  
END  

' 
END
GO
/****** Object:  Table [dbo].[SYS_Parameters]    Script Date: 04/29/2013 13:33:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SYS_Parameters]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[SYS_Parameters](
	[ParamCode] [nvarchar](32) NOT NULL,
	[ParamName] [nvarchar](64) NOT NULL,
	[ParamValue] [nvarchar](128) NOT NULL,
	[Remark] [nvarchar](255) NULL,
	[OrderIndex] [int] NULL,
 CONSTRAINT [PK_SYS_PARAMETERS] PRIMARY KEY CLUSTERED 
(
	[ParamCode] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO
IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_Description' , N'SCHEMA',N'dbo', N'TABLE',N'SYS_Parameters', N'COLUMN',N'ParamCode'))
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'参数编码' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'SYS_Parameters', @level2type=N'COLUMN',@level2name=N'ParamCode'
GO
IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_Description' , N'SCHEMA',N'dbo', N'TABLE',N'SYS_Parameters', N'COLUMN',N'ParamName'))
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'参数名称' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'SYS_Parameters', @level2type=N'COLUMN',@level2name=N'ParamName'
GO
IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_Description' , N'SCHEMA',N'dbo', N'TABLE',N'SYS_Parameters', N'COLUMN',N'ParamValue'))
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'参数值' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'SYS_Parameters', @level2type=N'COLUMN',@level2name=N'ParamValue'
GO
IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_Description' , N'SCHEMA',N'dbo', N'TABLE',N'SYS_Parameters', N'COLUMN',N'Remark'))
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'参数说明' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'SYS_Parameters', @level2type=N'COLUMN',@level2name=N'Remark'
GO
IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_Description' , N'SCHEMA',N'dbo', N'TABLE',N'SYS_Parameters', N'COLUMN',N'OrderIndex'))
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'排序' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'SYS_Parameters', @level2type=N'COLUMN',@level2name=N'OrderIndex'
GO
IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_Description' , N'SCHEMA',N'dbo', N'TABLE',N'SYS_Parameters', NULL,NULL))
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'系统参数表' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'SYS_Parameters'
GO
/****** Object:  Table [dbo].[AttachmentTypeInfo]    Script Date: 04/29/2013 13:33:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[AttachmentTypeInfo]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[AttachmentTypeInfo](
	[ID] [char](36) NULL,
	[TypeName] [nvarchar](50) NULL,
	[Description] [nvarchar](200) NULL,
	[AllowExt] [varchar](100) NULL,
	[MaxFileSize] [decimal](15, 2) NULL,
	[SaveDirName] [varchar](100) NULL
) ON [PRIMARY]
END
GO
SET ANSI_PADDING OFF
GO
IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_Description' , N'SCHEMA',N'dbo', N'TABLE',N'AttachmentTypeInfo', N'COLUMN',N'ID'))
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'类型ID' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'AttachmentTypeInfo', @level2type=N'COLUMN',@level2name=N'ID'
GO
IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_Description' , N'SCHEMA',N'dbo', N'TABLE',N'AttachmentTypeInfo', N'COLUMN',N'TypeName'))
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'类型名称' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'AttachmentTypeInfo', @level2type=N'COLUMN',@level2name=N'TypeName'
GO
IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_Description' , N'SCHEMA',N'dbo', N'TABLE',N'AttachmentTypeInfo', N'COLUMN',N'Description'))
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'类型描述' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'AttachmentTypeInfo', @level2type=N'COLUMN',@level2name=N'Description'
GO
IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_Description' , N'SCHEMA',N'dbo', N'TABLE',N'AttachmentTypeInfo', N'COLUMN',N'AllowExt'))
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'允许的附件类型' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'AttachmentTypeInfo', @level2type=N'COLUMN',@level2name=N'AllowExt'
GO
IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_Description' , N'SCHEMA',N'dbo', N'TABLE',N'AttachmentTypeInfo', N'COLUMN',N'MaxFileSize'))
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'单个附件大小限制' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'AttachmentTypeInfo', @level2type=N'COLUMN',@level2name=N'MaxFileSize'
GO
IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_Description' , N'SCHEMA',N'dbo', N'TABLE',N'AttachmentTypeInfo', N'COLUMN',N'SaveDirName'))
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'保存目录名称' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'AttachmentTypeInfo', @level2type=N'COLUMN',@level2name=N'SaveDirName'
GO
IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_Description' , N'SCHEMA',N'dbo', N'TABLE',N'AttachmentTypeInfo', NULL,NULL))
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'附件类型' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'AttachmentTypeInfo'
GO
/****** Object:  Table [dbo].[AttachmentInfo]    Script Date: 04/29/2013 13:33:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[AttachmentInfo]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[AttachmentInfo](
	[ID] [char](36) NOT NULL,
	[TypeID] [nvarchar](50) NOT NULL,
	[FileName] [nvarchar](200) NOT NULL,
	[FileUrl] [varchar](2000) NOT NULL,
	[FileSize] [decimal](18, 2) NULL,
	[FilePath] [nvarchar](500) NULL,
	[CreatorID] [char](36) NULL,
	[CreatorName] [nvarchar](20) NULL,
	[CreateDate] [datetime] NULL,
	[Mark1] [nvarchar](100) NULL,
	[Mark2] [nvarchar](100) NULL,
	[Mark3] [nvarchar](100) NULL,
	[Mark4] [nvarchar](100) NULL,
 CONSTRAINT [PK_AttachmentInfo] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_PADDING OFF
GO
IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_Description' , N'SCHEMA',N'dbo', N'TABLE',N'AttachmentInfo', N'COLUMN',N'ID'))
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'附件ID' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'AttachmentInfo', @level2type=N'COLUMN',@level2name=N'ID'
GO
IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_Description' , N'SCHEMA',N'dbo', N'TABLE',N'AttachmentInfo', N'COLUMN',N'TypeID'))
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'附件类型ID' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'AttachmentInfo', @level2type=N'COLUMN',@level2name=N'TypeID'
GO
IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_Description' , N'SCHEMA',N'dbo', N'TABLE',N'AttachmentInfo', N'COLUMN',N'FileName'))
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'附件原始文件名称' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'AttachmentInfo', @level2type=N'COLUMN',@level2name=N'FileName'
GO
IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_Description' , N'SCHEMA',N'dbo', N'TABLE',N'AttachmentInfo', N'COLUMN',N'FileUrl'))
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'附件Url' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'AttachmentInfo', @level2type=N'COLUMN',@level2name=N'FileUrl'
GO
IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_Description' , N'SCHEMA',N'dbo', N'TABLE',N'AttachmentInfo', N'COLUMN',N'FileSize'))
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'附件大小' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'AttachmentInfo', @level2type=N'COLUMN',@level2name=N'FileSize'
GO
IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_Description' , N'SCHEMA',N'dbo', N'TABLE',N'AttachmentInfo', N'COLUMN',N'FilePath'))
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'保存虚拟路径' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'AttachmentInfo', @level2type=N'COLUMN',@level2name=N'FilePath'
GO
IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_Description' , N'SCHEMA',N'dbo', N'TABLE',N'AttachmentInfo', N'COLUMN',N'CreatorID'))
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'创建人ID' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'AttachmentInfo', @level2type=N'COLUMN',@level2name=N'CreatorID'
GO
IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_Description' , N'SCHEMA',N'dbo', N'TABLE',N'AttachmentInfo', N'COLUMN',N'CreatorName'))
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'创建人名称' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'AttachmentInfo', @level2type=N'COLUMN',@level2name=N'CreatorName'
GO
IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_Description' , N'SCHEMA',N'dbo', N'TABLE',N'AttachmentInfo', N'COLUMN',N'CreateDate'))
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'上传日期' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'AttachmentInfo', @level2type=N'COLUMN',@level2name=N'CreateDate'
GO
IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_Description' , N'SCHEMA',N'dbo', N'TABLE',N'AttachmentInfo', N'COLUMN',N'Mark1'))
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'标识1' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'AttachmentInfo', @level2type=N'COLUMN',@level2name=N'Mark1'
GO
IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_Description' , N'SCHEMA',N'dbo', N'TABLE',N'AttachmentInfo', N'COLUMN',N'Mark2'))
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'标识2' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'AttachmentInfo', @level2type=N'COLUMN',@level2name=N'Mark2'
GO
IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_Description' , N'SCHEMA',N'dbo', N'TABLE',N'AttachmentInfo', N'COLUMN',N'Mark3'))
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'标识3' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'AttachmentInfo', @level2type=N'COLUMN',@level2name=N'Mark3'
GO
IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_Description' , N'SCHEMA',N'dbo', N'TABLE',N'AttachmentInfo', N'COLUMN',N'Mark4'))
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'标识4' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'AttachmentInfo', @level2type=N'COLUMN',@level2name=N'Mark4'
GO
IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_Description' , N'SCHEMA',N'dbo', N'TABLE',N'AttachmentInfo', NULL,NULL))
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'附件信息' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'AttachmentInfo'
GO
/****** Object:  StoredProcedure [dbo].[USP_GetFlowNumber]    Script Date: 04/29/2013 13:33:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[USP_GetFlowNumber]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[USP_GetFlowNumber]
AS
BEGIN
  DECLARE @Date VARCHAR(10)
  DECLARE @p0 AS int
  BEGIN TRAN
	SET @Date = CONVERT(VARCHAR(10), GETDATE(),112)
    UPDATE dbo.SYS_Parameters SET ParamValue = ParamValue + 1 WHERE ParamName = @Date AND ParamCode=''FlowNumber''
    IF @@ROWCOUNT = 0
    BEGIN
      DELETE FROM dbo.SYS_Parameters WHERE ParamCode=''FlowNumber''
      INSERT INTO SYS_Parameters(ParamCode,ParamName,ParamValue) VALUES(''FlowNumber'',@Date,1)
      SELECT @p0=1
    END
    ELSE
    BEGIN
      SELECT @p0=convert(int,ParamValue) from SYS_Parameters where ParamCode =''FlowNumber''		
    END
  COMMIT TRAN
  RETURN @p0
END
' 
END
GO
